﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;

namespace Siva.CBC.Barnacle.ashx
{
    public class SqlHelper
    {
        public static string GetSqlConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["NumDB"].ConnectionString;
        }

        public static int ExecuteNonQuery(string sqlText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(GetSqlConnectionString()))
            {
                using (MySqlCommand cmd = new MySqlCommand(sqlText, conn))
                {
                    cmd.Parameters.AddRange(parameters);  //对数据库使用参数进行赋值
                    conn.Open();  //打开数据库
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sqlText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(GetSqlConnectionString()))
            {
                using (MySqlCommand cmd = new MySqlCommand(sqlText, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteDataTable(string sqlText, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(GetSqlConnectionString()))
            {
                using (MySqlCommand command = new MySqlCommand(sqlText, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataAdapter和DataTable对象，并填充数据
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);
                        return dataTable;
                    }
                }
            }
        }

        // 执行一个查询，并将结果集映射到一个对象列表
        public List<T> ExecuteQuery<T>(string sql, Func<IDataRecord, T> selector, params MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(GetSqlConnectionString()))
            {
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    // 添加参数
                    command.Parameters.AddRange(parameters);
                    // 打开连接
                    connection.Open();
                    // 创建DataReader对象并读取数据，将每行数据映射到对象并添加到列表中
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        List<T> list = new List<T>();
                        while (reader.Read())
                        {
                            list.Add(selector(reader));
                        }
                        return list;
                    }
                }
            }
        }
    }
}